package com.goethe.translator;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class TranslationsOpenHelper extends SQLiteOpenHelper {
	
    private static int databaseVersion = 1;
    static final String DATABASE_NAME = "goethetranslator.db";
    static final String TRANSLATOR_TABLE_NAME = "Translations";
    static final String TAG = "TranslatorOpenHelper";
    static final String _ID = "_ID";
    static final String GERMAN_WORD = "GermanWord";
    static final String GERMAN_GENDER = "GermanGender";
    static final String GERMAN_PLURAL = "GermanPlural";
    static final String SPANISH_WORD = "SpanishWord";
    static final String SPANISH_GENDER = "SpanishGender";
    static final String SPANISH_PLURAL = "SpanishPlural";
    static final String WORD_TYPE = "WordType";
    static final String WORD_LEVEL = "WordLevel";
    static final String WORD_GROUP = "WordGroup";
    private static InputStream inputStream = null;
    
	TranslationsOpenHelper(Context context) {
		super(context, DATABASE_NAME, null, getDatabaseVersion(context));
		
    	// We check if the internal storage file exists
    	boolean useInternalStorage = true;
    	File file = context.getFileStreamPath((UpdaterHttpRequest.getTranslationsFileName()));
    	if(!file.exists()){
    		useInternalStorage = false;
    	}
    	
    	// We obtain the input stream
    	InputStream insertsStream = null;
    	if(useInternalStorage){
			try {
				insertsStream = getDownloadedTranslations(context);
			} catch (FileNotFoundException e) {
				Log.e(TAG, e.getMessage(), e);
				insertsStream = getBuiltInTranslations(context);
			}
    	}else{
    		insertsStream = getBuiltInTranslations(context);
    	}
		
        inputStream = insertsStream;
    }
	
	private InputStream getBuiltInTranslations(Context context){
		Log.d(TAG, "Taking builtin translations from /res/raw/translations_db");
		return context.getResources().openRawResource(R.raw.translations_db);
	}
	
	private InputStream getDownloadedTranslations(Context context) throws FileNotFoundException{
		Log.d(TAG, "Taking updated translations from internal storage");
		return context.openFileInput(UpdaterHttpRequest.getTranslationsFileName());
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + TRANSLATOR_TABLE_NAME + " ("
                + _ID + " INTEGER PRIMARY KEY,"
                + GERMAN_WORD + " TEXT,"
                + GERMAN_GENDER + " TEXT,"
                + GERMAN_PLURAL + " TEXT,"
                + SPANISH_WORD + " TEXT,"
                + SPANISH_GENDER + " TEXT,"
                + SPANISH_PLURAL + " TEXT,"
                + WORD_TYPE + " TEXT,"
                + WORD_LEVEL + " TEXT,"
                + WORD_GROUP + " TEXT"
                + ");");
        
        // Set of initial data to include in the dictionary
        try {
			int rows = insertFromStream(db, inputStream);
			Log.d(TAG, "Inserted " + rows + " rows into DB");
		} catch (IOException e) {
			Log.e(TAG, "Error inserting data into DB ", e);
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        // Logs that the database is being upgraded
        Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");

        // Kills the table and existing data
        db.execSQL("DROP TABLE IF EXISTS " + TRANSLATOR_TABLE_NAME);

        // Recreates the database with a new version
        onCreate(db);
	}
	
	// Reads a file from the given input stream and calls every line of it as SQL
	private int insertFromStream(SQLiteDatabase db, InputStream inputStream) throws IOException {
		
		// Reseting Counter
	    int result = 0;

	    // Open the resource
	    BufferedReader insertReader = new BufferedReader(new InputStreamReader(inputStream));

	    // Iterate through lines (assuming each insert has its own line and there is no other stuff)
	    while (insertReader.ready()) {
	        String insertStmt = insertReader.readLine();
	        db.execSQL(insertStmt);
	        result++;
	    }
	    
	    // We close the Buffered Reader and the SQLiteDatabase to avoid memory leaks
	    insertReader.close();

	    // returning number of inserted rows
	    return result;
	}
	
	private static String getDatabasePath(Context context){
		return context.getDatabasePath(DATABASE_NAME).getAbsolutePath();
	}
	
	private static int getDatabaseVersion(Context context){
		int dbVersionFromFile = SQLiteDatabase.openDatabase(getDatabasePath(context), null, SQLiteDatabase.OPEN_READONLY).getVersion();
		if(TranslationsOpenHelper.databaseVersion < dbVersionFromFile){
			return dbVersionFromFile;
		}else{
			return TranslationsOpenHelper.databaseVersion;
		}
	}
	
	public static void incrementDatabaseVersion(Context context){
		TranslationsOpenHelper.databaseVersion = SQLiteDatabase.openDatabase(getDatabasePath(context), null, SQLiteDatabase.OPEN_READONLY).getVersion()+1;
		Log.d(TAG, "Current DB version is: " + TranslationsOpenHelper.databaseVersion);
	}
}
